<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Star_model extends CI_Model {
	
	var $request_params;

	function __construct() {
		parent::__construct();

	}
	
	/**
	 * 스타 궁합 통계 저장
	 */
	function insert_update_star_statistics($service_id, $artist_idx) {
		log_message('debug',"#### Star_model::insert_update_star_statistics($service_id, $artist_idx)");
		
		$today = explode("-", date("Y-m-d", time()));
		$yyyy  = $today[0];
		$mm    = $today[1];
		$dd    = $today[2];
		
		$sql  = PHP_EOL." INSERT INTO tb_star_statistics ( ";
		$sql .= PHP_EOL."       service_id ";
		$sql .= PHP_EOL."     , year ";
		$sql .= PHP_EOL."     , month ";
		$sql .= PHP_EOL."     , day ";
		$sql .= PHP_EOL."     , artist_idx ";
		$sql .= PHP_EOL."     , cnt ";
		$sql .= PHP_EOL." ) VALUES ( ";
		$sql .= PHP_EOL."       '$service_id' ";
		$sql .= PHP_EOL."     , '$yyyy' ";
		$sql .= PHP_EOL."     , '$mm' ";
		$sql .= PHP_EOL."     , '$dd' ";
		$sql .= PHP_EOL."     , '$artist_idx' ";
		$sql .= PHP_EOL."     , '1' ";
		$sql .= PHP_EOL." ) ON DUPLICATE KEY UPDATE ";
		$sql .= PHP_EOL."       service_id = '$service_id' ";
		$sql .= PHP_EOL."     , year = '$yyyy' ";
		$sql .= PHP_EOL."     , month = '$mm' ";
		$sql .= PHP_EOL."     , day = '$dd' ";
		$sql .= PHP_EOL."     , artist_idx = '$artist_idx' ";
		$sql .= PHP_EOL."     , cnt = cnt+1 ";
		$sql .= PHP_EOL." ; ";
		
		$this->db->query($sql) or $this->logsql->log_sql('error');

	}

	/**
	 * 스타 궁합 점수 저장
	 */
	function insert_star_gunghap_ranking($user_id, $artist_idx, $score) {
		log_message('debug',"#### Star_model::insert_star_gunghap_ranking($user_id, $artist_idx, $score)");
		
		$this->db->set('user_id'   , $user_id);
		$this->db->set('artist_idx', $artist_idx);
		$this->db->set('score'     , $score);
		$this->db->set('create_dt' , 'NOW()', FALSE);
		$this->db->insert('tb_star_gunghap_ranking');
		
	}

	/**
	 * 최근 스타 궁합 점수 리스트가져오기
	 */
	function select_star_gunghap_result_list() {
		log_message('debug',"#### Star_model::select_star_gunghap_result_list()");
		
		$sql  = PHP_EOL." SELECT A.idx ";
		$sql .= PHP_EOL."     , A.user_id ";
		$sql .= PHP_EOL."     , (SELECT nickname FROM tb_member WHERE user_id = A.user_id ) nickname ";
		$sql .= PHP_EOL."     , A.artist_idx ";
		$sql .= PHP_EOL."     , (SELECT artist_name FROM tb_artist WHERE idx = A.artist_idx ) artist_name ";
		$sql .= PHP_EOL."     , A.score ";
		$sql .= PHP_EOL." FROM tb_star_gunghap_ranking A";
		$sql .= PHP_EOL." ORDER BY idx DESC ";
		$sql .= PHP_EOL." LIMIT 0,5 ";
		$query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 총 내역 수
	 */
	function get_star_gunghap_total_count() {
		$this->db->from('tb_star_gunghap_ranking');
		$result = $this->db->count_all_results();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	
	/**
	 * 스타 랭킹(월별 많이 본 스타) 리스트가져오기
	 */
	function select_star_ranking_list($service_id, $date) {
		log_message('debug',"#### Star_model::select_star_ranking_list($service_id, $date)");
		
		$year  = substr($date, 0, 4);
		$month = substr($date, 4, 2);
		
		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       S.artist_idx ";
		$sql .= PHP_EOL."     , A.artist_name ";
		$sql .= PHP_EOL."     , A.job ";
		$sql .= PHP_EOL."     , S.year ";
		$sql .= PHP_EOL."     , S.month ";
		$sql .= PHP_EOL."     , SUM(S.cnt) cnt ";
		$sql .= PHP_EOL." FROM  tb_star_statistics S ";
		$sql .= PHP_EOL." INNER JOIN tb_artist A ON A.idx = S.artist_idx ";
		$sql .= PHP_EOL." WHERE S.year = ? ";
		$sql .= PHP_EOL." AND   S.month = ? ";
		$sql .= PHP_EOL." AND   S.service_id = ? ";
		$sql .= PHP_EOL." GROUP BY S.service_id, S.year, S.month, S.artist_idx ";
		$sql .= PHP_EOL." ORDER BY SUM(S.cnt) DESC ";
		$sql .= PHP_EOL." LIMIT 0, 5 ";
		$query = $this->db->query($sql, array($year, $month, $service_id)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
	
	
	/**
	 * 스타 정보 가져오기
	 */
	function get_artist($idx) {
		log_message('debug',"#### Star_model::get_artist($idx)");
		
		$query = $this->db->get_where('tb_artist',array('idx'=>$idx)) or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		$result['job_icon'] = $this->get_star_job_icon($result['job']);

		return $result;
	}

	/**
	 * 스타 간략 정보 리스트 검색
	 */
	function select_artist_simple_list($birtdate) {
		log_message('debug',"#### Star_model::select_artist_simple_list($birtdate)");

		$this->db->select('idx, artist_name, job, birth_yyyy, birth_mm, birth_dd, sex, body, photo');
		$this->db->where('birth_mm', "SUBSTRING('$birtdate',6,2)", FALSE);
		$this->db->where('birth_dd', "SUBSTRING('$birtdate',9,2)", FALSE);
		$query = $this->db->get('tb_artist') or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		foreach ($result as $key => $star) {
			$result[$key]['job_icon'] = $this->get_star_job_icon($star['job']);
		}		

		$this->logsql->log_result_with_title('아이콘 추가 결과',$result);

		return $result;
	}

	/**
	 * 인기스타 리스트
	 */
	function select_popular_star_list($popular_id) {
		log_message('debug',"#### Star_model::select_popular_star_list($popular_id)");

		$this->db->select('P.priority, A.idx, A.artist_name, A.job, A.birth_yyyy, A.birth_mm, A.birth_dd, A.sex, A.body, A.photo');
		$this->db->from('tb_popular_star P');
		$this->db->join('tb_artist A', 'P.artist_idx = A.idx');
		$this->db->where('P.popular_id', $popular_id);
		$this->db->where('P.use_yn', 'Y');
		$this->db->order_by('P.priority','ASC');
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		foreach ($result as $key => $star) {
			$result[$key]['job_icon'] = $this->get_star_job_icon($star['job']);
		}

		return $result;
	}

	/**
	 * 스타 리스트 검색 (이름)
	 */
	function select_artist_search_list($search_keyword) {
		log_message('debug',"#### Star_model::select_artist_search_list($search_keyword)");

		$this->db->select('idx, artist_name, job, birth_yyyy, birth_mm, birth_dd, sex, body, photo');
		$this->db->like('keyword', $search_keyword);
		$query = $this->db->get('tb_artist') or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}


	/**
	 * 나와 같은 운명의 스타 리스트
	 */
	function select_like_artist_list($table_name, $db_express) {
		log_message('debug',"#### Star_model::select_like_artist_list($table_name, $db_express)");
		
		$sql  = PHP_EOL." SELECT idx, artist_name, job, birth_yyyy, birth_mm, birth_dd, sex, body ";
		$sql .= PHP_EOL." FROM tb_artist ";
		$sql .= PHP_EOL." WHERE idx in ( ";
		$sql .= PHP_EOL."         SELECT idx ";
		$sql .= PHP_EOL."         FROM tb_artist_express ";
		$sql .= PHP_EOL."         WHERE table_name = ? ";
		$sql .= PHP_EOL."         AND   db_express = ? ";
		$sql .= PHP_EOL." ) ";
		$query = $this->db->query($sql, array($table_name, $db_express)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		foreach ($result as $key => $star) {
			$result[$key]['job_icon'] = $this->get_star_job_icon($star['job']);
		}		
		

		return $result;
	}
	
	/**
	 * 스타의 직업명으로 해당하는 아이콘명을 찾아준다.
	 */
	function get_star_job_icon($star_job) {
		log_message('debug',"#### Star_model::get_star_job_icon($star_job)");
		
		$arr_star_job = explode(',', $star_job);
				
		$job_list['actor']      = array('영화배우', '영화감독', '영화인', '영화제작자', '영화촬영감독');
		$job_list['gag']        = array('개그맨', '코미디언');
		$job_list['sports']     = array('운동선수', '스케이트선수', '농구선수', '야구선수', '카레이서', '축구선수', '골프선수', '프로야구 선수');
		$job_list['mc']         = array('MC', 'VJ', '방송인', '아나운서');
		$job_list['misskorea']  = array('미스코리아', '모델');
		$job_list['musical']    = array('뮤지컬배우', '연극배우');
		$job_list['politician'] = array('국회의원', '공무원', '금융인', '기업인', '대학교수', '교수', '문화기관단체인', '연세대 교회음악과 교수', '정당인');
		$job_list['singer']     = array('가수', '바이올리니스트', '대중음악작곡가', '성악가', 'DJ', '국립합창단 예술감독', '음악PD', '오페라가수', '음악감독', '작곡가', '작사가', '지휘자', '첼리스트');
		$job_list['talent']     = array('탤런트', '방송연예인', '연예인');
		$job_list['and']        = array('그밖에', '방송작가', '사진작가', 'PD', '만화가', '공연기획자', '연예기획자', '작가', '촬영감독');
		
		foreach ($arr_star_job as $value) {
			$value = trim($value);
			
			foreach ($job_list as $key => $item) {
				if (in_array($value, $item)) {
					return $key;
				}
			}
		}
		
		return 'and';
	}
}

/* End of file star_model.php */
/* Location: ./application/models/unse/star_model.php */